package com.ztk.threadbasic.excel.demo;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.ztk.threadbasic.enums.ExcelEnum;
import com.ztk.threadbasic.excel.CompanyVO;
import com.ztk.threadbasic.excel.handler.CustomCellStyleStrategy;
import com.ztk.threadbasic.excel.handler.CustomCellWidthHanlder;
import com.ztk.threadbasic.excel.handler.CustomMergeStrategy;
import com.ztk.threadbasic.excel.handler.ExampleDataRedColorHandler;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;

@RestController
public class WebUploadAndDownload {

    /**
     * 文件下载
     * 1. 编写实体类并创建对象以便写入表格
     * 2. 设置响应参数：文件的ContentType和文件名，同时设置编码避免乱码
     * 3. 直接写，内部会调用finish方法自动关闭OutputStream
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");

        InputStream templateExcelInputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream("template/excel/response.xls");

        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), Student.class)
                .withTemplate(templateExcelInputStream);

        excelWriterBuilder.sheet("模板").doFill(initData());
    }

    /**
     * 文件下载
     * 1. 编写实体类并创建对象以便写入表格
     * 2. 设置响应参数：文件的ContentType和文件名，同时设置编码避免乱码
     * 3. 直接写，内部会调用finish方法自动关闭OutputStream
     */
    @GetMapping("/download2")
    public ResponseEntity<InputStreamResource> download2(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        ExcelWriterBuilder workBook = EasyExcel.write(response.getOutputStream(), Student.class);

        ExcelWriterSheetBuilder sheet = workBook.sheet("模板");

        sheet.doWrite(initData());

        return ResponseEntity.status(ExcelEnum.OK.getCode()).build();
    }

    /**
     * 文件下载
     * 1. 编写实体类并创建对象以便写入表格
     * 2. 设置响应参数：文件的ContentType和文件名，同时设置编码避免乱码
     * 3. 直接写，内部会调用finish方法自动关闭OutputStream
     */
    @GetMapping("/download3")
    public ResponseEntity<InputStreamResource> download3(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        InputStream templateExcelInputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream("template/excel/response.xls");

        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), Student.class)
                .withTemplate(templateExcelInputStream);

        excelWriterBuilder.sheet("模板").doFill(initData());

        return ResponseEntity.status(ExcelEnum.OK.getCode()).build();
    }


    /**
     * 文件下载
     * 1. 编写实体类并创建对象以便写入表格
     * 2. 设置响应参数：文件的ContentType和文件名，同时设置编码避免乱码
     * 3. 直接写，内部会调用finish方法自动关闭OutputStream
     */
    @GetMapping("/download4")
    public ResponseEntity<InputStreamResource> download4(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        InputStream templateExcelInputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream("template/excel/cccc.xlsx");


        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), CompanyVO2.class)
                .withTemplate(templateExcelInputStream);

        ExcelWriterSheetBuilder muban = excelWriterBuilder.sheet("muban");


        List<CompanyVO2> list = initVO();

        muban.doFill(list);

        return ResponseEntity.status(ExcelEnum.OK.getCode()).build();
    }

    @GetMapping("/download5")
    public ResponseEntity<InputStreamResource> download5(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        InputStream templateExcelInputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream("template/excel/cccc.xlsx");

        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), CompanyVO2.class)
                .withTemplate(templateExcelInputStream);

        excelWriterBuilder.sheet().doFill(initVO());

        return ResponseEntity.status(ExcelEnum.OK.getCode()).build();
    }

    @GetMapping("/download6")
    public ResponseEntity<InputStreamResource> download6(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        InputStream templateExcelInputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream("template/excel/cccc2.xlsx");

        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), CompanyVO2.class)
                .withTemplate(templateExcelInputStream);

        ExcelWriter excelWriter = excelWriterBuilder.build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();

        List<CompanyVO2> list = initVO();

        Map<String, String> firstLineData = new HashMap<>();
        firstLineData.put("budgetAgency", "总公司");
        firstLineData.put("budgetDate", "2024/02/01");

        excelWriter.fill(firstLineData, writeSheet);
        excelWriter.fill(list, writeSheet);

        excelWriter.finish();

        return ResponseEntity.status(ExcelEnum.OK.getCode()).build();
    }

    @GetMapping("/download7")
    public ResponseEntity<InputStreamResource> download7(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        InputStream templateExcelInputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream("template/excel/cccc.xlsx");

        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), CompanyVO2.class)
                .withTemplate(templateExcelInputStream)
                .registerWriteHandler(new ExampleDataRedColorHandler());

        excelWriterBuilder.sheet().doFill(initVO());

        return ResponseEntity.status(ExcelEnum.OK.getCode()).build();
    }

    @GetMapping("/download8")
    public ResponseEntity<InputStreamResource> download8(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        InputStream templateExcelInputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream("template/excel/cccc.xlsx");

        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), CompanyVO2.class)
                .withTemplate(templateExcelInputStream)
                .registerWriteHandler(new ExampleDataRedColorHandler())
                .registerWriteHandler(new CustomCellStyleStrategy());

        excelWriterBuilder.sheet().doFill(initVO());

        return ResponseEntity.status(ExcelEnum.OK.getCode()).build();
    }

    /**
     * 设置下载的excel的第一列字体加粗
     * @param response
     * @return
     * @throws IOException
     */
    @GetMapping("/download9")
    public ResponseEntity<InputStreamResource> download9(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        InputStream templateExcelInputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream("template/excel/cccc.xlsx");

        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), CompanyVO2.class)
                .withTemplate(templateExcelInputStream)
                .registerWriteHandler(new ExampleDataRedColorHandler());

        excelWriterBuilder.sheet().doFill(initVO());

        return ResponseEntity.status(ExcelEnum.OK.getCode()).build();
    }


    private static List<Student> initData() {
        ArrayList<Student> students = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Student data = new Student();
            data.setName2("杭州黑马学号0" + i);
            data.setBirthday(new Date());
            data.setGender("男");
            students.add(data);
        }
        return students;
    }

    private List<CompanyVO2> initVO() {
        List<CompanyVO2> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            CompanyVO2 companyVO = new CompanyVO2();
            companyVO.setProject("保费" + i);
            companyVO.setProjectDetail("细节" + i);
            companyVO.setEntirety("5632.98454");
            companyVO.setCreditInsuranceSector("32.0345445");
            list.add(companyVO);
        }
        return list;
    }
}
